import json
import os
from datetime import datetime
from django.utils import timezone

from django.db import connection
from django.http import JsonResponse

from . import models
from .models import Patient, PatientBolus, PatientMeal


def save_patient_info(full_name, patient_number, age, weight, TDD, basalInsulin):
    try:
        # 使用 update_or_create 来查找患者并进行更新或创建
        patient, created = Patient.objects.update_or_create(
            patient_number=patient_number,  # 根据 patient_number 查找
            defaults={  # 如果找到了，更新这些字段
                'full_name': full_name,
                'age': age,
                'weight': weight,
                'tdd': TDD,
                'basal_insulin': basalInsulin
            }
        )

        # 如果是首次创建患者记录
        if created:
            # 动态表名为患者编号（patient_number）
            table_name = f'cgm_{patient_number}'  # 表名为患者编号，避免与其他表冲突

            # 检查表是否已经存在
            with connection.cursor() as cursor:
                cursor.execute(f"""
                    SELECT * 
                    FROM information_schema.tables 
                    WHERE table_name = '{table_name}';
                """)
                result = cursor.fetchone()

                # 如果表不存在，创建表
                if not result:
                    create_table_sql = f"""
                    CREATE TABLE {table_name} (
                        sg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 时间戳字段
                        sgv INTEGER NOT NULL  -- 整数字段
                    );
                    """
                    cursor.execute(create_table_sql)  # 执行 SQL 创建表
                    return True, None  # 返回创建成功
                else:
                    return True, f"Table {table_name} already exists."  # 如果表已经存在

        else:
            return True, "Patient record updated."  # 如果是更新了现有记录

    except Exception as e:
        return False, f"An error occurred: {str(e)}"  # 捕获其他异常





def save_bolus_record(patient_number, bolus):
    try:
        # 创建并保存 bolus 记录
        bolus_record = PatientBolus(
            patient_number=patient_number,
            time=datetime.now(),  # 当前时间
            bolus=bolus
        )
        bolus_record.save()  # 保存到数据库
        return True, None  # 返回成功标志
    except Exception as e:
        return False, str(e)  # 返回失败标志和错误信息


def save_meal_record(patient_number, meal_type, cho, pre_meal_bolus, notes=None, meal_datetime=None):
    try:
        # 创建并保存餐食记录
        record_time = meal_datetime if meal_datetime is not None else timezone.now()
        
        meal_record = PatientMeal(
            patient_number=patient_number,
            time=record_time,  # 使用传入的时间或当前时间
            meal_type=meal_type,
            cho=cho,
            pre_meal_bolus=pre_meal_bolus,
            notes=notes
        )
        meal_record.save()  # 保存到数据库
        return True, None  # 返回成功标志
    except Exception as e:
        return False, str(e)  # 返回失败标志和错误信息


def get_patient_info(patient_id):
    """
    获取患者的基本信息
    """
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT Full_name, Patient_Number, age, Weight, tdd, basalInsulin
            FROM patient_info
            WHERE Patient_Number = %s
        """, [patient_id])
        result = cursor.fetchone()
    return result


def get_latest_cgm_entry(patient_id):
    """
    获取患者最新的CGM记录
    返回：(最新血糖值, 最新时间)
    """
    try:
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT sgv, sg_date 
                FROM cgm_{patient_id} 
                ORDER BY sg_date DESC 
                LIMIT 1;
            """.format(patient_id=patient_id))
            result = cursor.fetchone()

        if result:
            latest_sgv, latest_sg_date = result
            return latest_sgv, latest_sg_date
        else:
            return None, None
    except Exception as e:
        print(f"Error getting latest CGM entry: {e}")
        return None, None


def fetch_cgm_data(patient_id, start_time=None, hours=24):
    """
    获取患者的CGM数据
    如果提供start_time，则从该时间点开始获取
    否则获取最近hours小时的数据
    """
    table_name = f"cgm_{patient_id}"
    try:
        with connection.cursor() as cursor:
            if start_time:
                cursor.execute(f"""
                    SELECT sg_date, sgv 
                    FROM {table_name}
                    WHERE sg_date >= %s
                    ORDER BY sg_date ASC;
                """, [start_time])
            else:
                from datetime import datetime, timedelta
                end_time = datetime.now()
                start_time = end_time - timedelta(hours=hours)
                cursor.execute(f"""
                    SELECT sg_date, sgv 
                    FROM {table_name}
                    WHERE sg_date BETWEEN %s AND %s
                    ORDER BY sg_date ASC;
                """, [start_time, end_time])
                
            results = cursor.fetchall()
        return [(item[0], item[1]) for item in results]
    except Exception as e:
        print(f"Error fetching CGM data: {e}")
        return []
